package smb.service;

import lombok.AllArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import smb.dao.StudentDao;
import smb.domain.Student;
import smb.mapper.StudentMapper;
import smb.vo.*;

import javax.persistence.EntityNotFoundException;
import java.util.List;
import java.util.Optional;

@Service
@AllArgsConstructor
public class StudentService {
    private JdbcTemplate jdbcTemplate;
    private StudentDao studentDao;
    private StudentMapper studentMapper;

    public StudentVo getStudent(String id) {
        Optional<Student> student = studentDao.findById(id);
        if (student.isPresent()) {
            return studentMapper.studentToStudentVo(student.get());
        } else {
            throw new EntityNotFoundException();
        }
    }

    public List<SectionScore> getScores(String id) {
        String sql = "SELECT section.id, section.size, section.semester,\n" +
                "       course.id, course.name,\n" +
                "       score.usual, score.midterm, score.experiment, score.terminal, score.total, score.time_stamp\n" +
                "FROM section\n" +
                "JOIN course on section.cou_id = course.id\n" +
                "JOIN score ON section.id = score.sec_id\n" +
                "JOIN student ON score.stu_id = student.id\n" +
                "WHERE student.id = ?\n" +
                "ORDER BY section.semester DESC ";
        return jdbcTemplate.query(sql, new Object[]{id}, new SectionScore.rowMapper());
    }
}
